package com.bingxue.edu.management.education.service.exam;

import com.alibaba.excel.EasyExcel;
import com.bingxue.edu.framework.common.util.ValidationUtils;
import com.bingxue.edu.framework.exception.BusinessException;
import com.bingxue.edu.management.education.model.aggregate.ExamRecordAggregate;
import com.bingxue.edu.management.education.model.entity.exam.Exam;
import com.bingxue.edu.management.education.model.entity.exam.ExamAnswerRecord;
import com.bingxue.edu.management.education.model.entity.exam.ExamQuestion;
import com.bingxue.edu.management.education.model.entity.exam.ExamRecord;
import com.bingxue.edu.management.system.service.DictItemService;
import com.mybatisflex.core.query.QueryWrapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

import static com.bingxue.edu.management.education.model.entity.exam.table.ExamAnswerRecordTableDef.EXAM_ANSWER_RECORD;
import static com.bingxue.edu.management.education.model.entity.exam.table.ExamQuestionTableDef.EXAM_QUESTION;
import static com.bingxue.edu.management.education.model.entity.exam.table.ExamRecordTableDef.EXAM_RECORD;
import static com.bingxue.edu.management.education.model.entity.exam.table.ExamTableDef.EXAM;


@Service
@Slf4j
@RequiredArgsConstructor
public class ExamExcelService {
    private final ExamAnswerRecordService examAnswerRecordService;
    private final ExamService examService;
    private final ExamRecordService examRecordService;
    private final ExamQuestionService examQuestionService;
    private final DictItemService dictItemService;
    public void importData(MultipartFile file) {
        checkIsExcel(file);
        ExamRecordDataListener importListener = new ExamRecordDataListener(dictItemService);
        try {
            EasyExcel.read(file.getInputStream(), importListener).sheet().headRowNumber(6).doReadSync();
            String examName = importListener.getExamName();
            //先在数据库中查找是否有考试信息,没有则用req中的exam
            QueryWrapper examQuery = QueryWrapper.create()
                    .select(EXAM.ALL_COLUMNS)
                    .where(EXAM.NAME.eq(examName));
            Exam exam = examService.getOne(examQuery);
            ValidationUtils.throwIfNull(exam,"该考试不存在,请先录入考试信息");
            importListener.setExam(exam);
            // 重新上传,清空之前的考试数据
            this.clearExamRecordData(exam.getId());
            // 创建题目
            List<ExamQuestion> questionList = importListener.getQuestionList();
            examQuestionService.saveList(questionList);
            // 创建记录
            List<ExamRecordAggregate> examRecordAggregates = importListener.getExamRecordAggregateList();
            for (ExamRecordAggregate examRecordAggregate : examRecordAggregates) {
                this.createRecordByAggregate(examRecordAggregate);
            }
            // 计算排名等未录入字段更新至数据库
            this.computeRank(exam.getId());

            log.info("导入考试数据成功: {}", examName);
        } catch (IOException e) {
            log.warn("解析文件失败", e);
            throw new BusinessException("解析文件失败");
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public void clearExamRecordData(String examId) {
        ValidationUtils.throwIfNull(examId,"没有该考试信息,无法清空已录入的数据");
        // 删除学生考试记录
        examRecordService.removeByExamId(examId);

        //删除问题记录
        examAnswerRecordService.removeByExamId(examId);
        
        // 删除问题
        examQuestionService.removeByExamId(examId);
    }

    /**
     * 计算排名
     * 计算exam_record的total_score和rank字段的值并更新
     * 计算exam的student_count,total_score,highest_score,lowest_score,average_score
     * 计算exam_question的average_score
     * @param examId
     */
    @Transactional(rollbackFor = Exception.class)
    public void computeRank(String examId) {
        Exam exam = examService.getById(examId);
        ValidationUtils.throwIfNull(exam, "考试数据未找到");

        QueryWrapper examRecordQueryWrapper = QueryWrapper.create()
                .select(EXAM_RECORD.ALL_COLUMNS)
                .where(EXAM_RECORD.EXAM_ID.eq(examId));
        List<ExamRecord> recordList = examRecordService.list(examRecordQueryWrapper);
        List<ExamRecord> sortedRecordList = recordList.stream()
                .sorted(Comparator.comparing(ExamRecord::getTotalScore).reversed())
                .collect(Collectors.toList());
        int studentCount = recordList.size();
        //exam的highest_score
        ExamRecord highestScoreRecord = sortedRecordList.get(0);
        //exam的lowest_score
        ExamRecord lowestScoreRecord = sortedRecordList.get(sortedRecordList.size() - 1);
        BigDecimal totalScore = BigDecimal.valueOf(0.0);
        int rank = 1;
        for (ExamRecord record : sortedRecordList) {
            // 总分求和
            BigDecimal score = record.getTotalScore();
            totalScore = totalScore.add(score);
            record.setRank(rank);
            examRecordService.updateById(record);
            rank++;
        }

        //计算试卷满分,exam的total_score字段赋值
        QueryWrapper examQuestionQueryWrapper = QueryWrapper.create()
                .select(EXAM_QUESTION.ALL_COLUMNS)
                .where(EXAM_QUESTION.EXAM_ID.eq(examId));
        List<ExamQuestion> examQuestions = examQuestionService.list(examQuestionQueryWrapper);
        BigDecimal perfectScore = BigDecimal.valueOf(0.0);
        for (ExamQuestion examQuestion:examQuestions){
            //计算满分
            perfectScore = perfectScore.add(examQuestion.getScore());

            //计算每题平均分,给exam_question的average_score赋值
            QueryWrapper examAnswerRecordQueryWrapper = QueryWrapper.create()
                    .select(EXAM_ANSWER_RECORD.ALL_COLUMNS)
                    .where(EXAM_ANSWER_RECORD.EXAM_ID.eq(examId))
                    .and(EXAM_ANSWER_RECORD.QUESTION_ID.eq(examQuestion.getId()));
            List<ExamAnswerRecord> examAnswerRecords = examAnswerRecordService.list(examAnswerRecordQueryWrapper);
            BigDecimal totalSingleScore = BigDecimal.valueOf(0.0);
            for (ExamAnswerRecord examAnswerRecord:examAnswerRecords){
                totalSingleScore = totalSingleScore.add(examAnswerRecord.getScore());
            }
            BigDecimal averageScore = totalSingleScore.divide(BigDecimal.valueOf(studentCount));
            examQuestion.setAverageScore(averageScore);
            examQuestionService.updateById(examQuestion);
        }
        exam.setTotalScore(perfectScore);
        //计算平均分,exam的average_score字段赋值
        BigDecimal averageScore = studentCount > 0 ? totalScore.divide(BigDecimal.valueOf(studentCount)) : BigDecimal.valueOf(0.0);
        exam.setAverageScore(averageScore);
        //exam的student_count字段赋值
        exam.setStudentCount(studentCount);
        exam.setHighestScore(highestScoreRecord.getTotalScore());
        exam.setLowestScore(lowestScoreRecord.getTotalScore());
        examService.updateById(exam);


        log.info("计算排名数据成功, {}", examId);
    }

    /**
     * 将考试记录和答题记录存入数据库中
     * @param examRecordAggregate 考试记录和答题记录
     */
    @Transactional(rollbackFor = Exception.class)
    public void createRecordByAggregate(ExamRecordAggregate examRecordAggregate) {
        //保存考试记录
        ExamRecord examRecord = examRecordAggregate.getExamRecord();
        examRecordService.save(examRecord);
        //保存答题记录
        List<ExamAnswerRecord> answerRecordList = examRecordAggregate.getAnswerRecordList();
        for (ExamAnswerRecord examAnswerRecord:answerRecordList){
            examAnswerRecord.setExamRecordId(examRecord.getId());
        }
        examAnswerRecordService.saveBatch(answerRecordList);

    }


    /**
     * 检查参数是否为excel文件
     * @param file
     */
    private void checkIsExcel(MultipartFile file) {
            ValidationUtils.throwIf(file==null, "未获取待处理的excel文件！");
            String fileName = file.getOriginalFilename();
            if (fileName!=null && isExcelFile(fileName)) {
                ValidationUtils.throwIf(file.isEmpty(), "该文件无内容！");
            } else {
                throw new BusinessException("请上传合法的Excel格式文件！");
            }

    }

    /**
     * 判断文件名是否为excel文件
     * @param fileName
     * @return
     */
    public static boolean isExcelFile(String fileName) {
        if (StringUtils.hasText(fileName)) {
            String extension = StringUtils.getFilenameExtension(fileName);
            return "xls".equalsIgnoreCase(extension) || "xlsx".equalsIgnoreCase(extension);
        }
        return false;
    }
}
